package com.ly.mp.dal.comm.jdbc;

import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.ly.mp.component.entities.PageInfo;

import io.seata.rm.datasource.DataSourceProxy;

public class PagedJdbcTemplate extends NamedParameterJdbcTemplate {
    private static final Logger logger = LoggerFactory.getLogger(PagedJdbcTemplate.class);
    public static final String DB_TYPE_ORACLE = "oracle";
    public static final String DB_TYPE_MYSQL = "mysql";
    public static final String DB_TYPE_SQLSERVER = "sqlserver";
    private boolean lowerCaseColumn = false;
    private DynamicDataSource dynamicDatasource;
    private final JdbcOperations classicJdbcTemplate;

    public PagedJdbcTemplate(DynamicDataSource dataSource) {
        super(dataSource);
        this.dynamicDatasource = dataSource;
        this.classicJdbcTemplate = new MpJdbcTemplate(dataSource);
    }

    public PagedJdbcTemplate(DynamicDataSource dataSource, String transactionPolicy) {
        super(dataSource);
        this.dynamicDatasource = dataSource;
        this.classicJdbcTemplate = new MpJdbcTemplate(dataSource, transactionPolicy);
    }

    @Override
    public JdbcOperations getJdbcOperations() {
        return this.classicJdbcTemplate;
    }

    public DataSource getDataSource() {
        return this.dynamicDatasource.getTargetDataSource();
    }

    public String getDBType() {
        return getDBType(this.dynamicDatasource);
    }

    public static String getDBType(DynamicDataSource datasource) {
        String jdbcUrl = getJdbcUrl(datasource);
        if (jdbcUrl.contains(DB_TYPE_ORACLE)) {
            return DB_TYPE_ORACLE;
        } else if (jdbcUrl.contains(DB_TYPE_MYSQL)) {
            return DB_TYPE_MYSQL;
        } else if (jdbcUrl.contains(DB_TYPE_SQLSERVER)) {
            return DB_TYPE_SQLSERVER;
        } else {
            throw new RuntimeException("不支持的数据库类型, 只支持oracle、sqlserver和mysql!");
        }
    }

    private static String getJdbcUrl(DynamicDataSource datasource) {
        DataSource dataSource = datasource.getTargetDataSource();
        String url = "";
        if (dataSource instanceof DruidDataSource) {
            DruidDataSource poolDatasource = (DruidDataSource) datasource.getTargetDataSource();
            url = poolDatasource.getUrl().toLowerCase();
        } else if (dataSource instanceof DataSourceProxy) {
            DataSourceProxy ds = (DataSourceProxy) dataSource;
            DruidDataSource druidDataSource = (DruidDataSource) ds.getTargetDataSource();
            url = druidDataSource.getUrl().toLowerCase();
        } else if (dataSource instanceof AtomikosDataSourceBean) {
            AtomikosDataSourceBean poolDatasource = (AtomikosDataSourceBean) datasource.getTargetDataSource();
            DruidXADataSource xaDatasource = (DruidXADataSource) poolDatasource.getXaDataSource();
            url = xaDatasource.getUrl().toLowerCase();
        } else if (dataSource instanceof DriverManagerDataSource) {
            DriverManagerDataSource driverManagerDataSource = (DriverManagerDataSource) datasource
                    .getTargetDataSource();
            url = driverManagerDataSource.getUrl();
        } 
//        else if (dataSource instanceof ShardingDataSource) {
//            ShardingDataSource shardDS = (ShardingDataSource) dataSource;
//            Object[] keys = shardDS.getDataSourceMap().keySet().toArray();
//            DataSourceProxy ds = (DataSourceProxy) shardDS.getDataSourceMap().get(keys[0]);
//            DruidDataSource druidDataSource = (DruidDataSource) ds.getTargetDataSource();
//            url = druidDataSource.getUrl().toLowerCase();
//        }

        return url;
    }

    public List<Map<String, Object>> queryForListPaged(String sql, Map<String, Object> paramMap, PageInfo pageInfo) {
        String sqlPaged = this.getPagedSql(sql, pageInfo, paramMap);
        List<Map<String, Object>> data = this.queryForList(sqlPaged, paramMap);
        if (data != null) {
            Iterator var6 = data.iterator();

            while (var6.hasNext()) {
                Map<String, Object> row = (Map) var6.next();
                if (!row.containsKey("ROW_NUM")) {
                    break;
                }

                row.remove("ROW_NUM");
            }
        }

        return data;
    }

    public List<Map<String, Object>> queryForListPagedEx(String sql, String countSql, Map<String, Object> paramMap,
            PageInfo pageInfo) {
        String sqlPaged = this.getPagedSqlEx(sql, countSql, pageInfo, paramMap);
        List<Map<String, Object>> data = this.queryForList(sqlPaged, paramMap);
        if (data != null) {
            Iterator var7 = data.iterator();

            while (var7.hasNext()) {
                Map<String, Object> row = (Map) var7.next();
                if (!row.containsKey("ROW_NUM")) {
                    break;
                }

                row.remove("ROW_NUM");
            }
        }

        return data;
    }

    public <T> List<T> queryForListPagedEx(String sql, String countSql, Map<String, Object> paramMap, PageInfo pageInfo,
            RowMapper<T> rowMapper) {
        String sqlPaged = this.getPagedSqlEx(sql, countSql, pageInfo, paramMap);
        return this.query(sqlPaged, paramMap, rowMapper);
    }

    public <T> List<T> queryForListPaged(String countsql, String sql, Map<String, Object> countparamMap,
            Map<String, Object> paramMap, PageInfo pageInfo, RowMapper<T> rowMapper) throws Exception {
        String sqlPaged = this.getPagedSql(countsql, sql, pageInfo, countparamMap, paramMap);
        List<T> data = this.query(sqlPaged, paramMap, rowMapper);
        return data;
    }

    private String getPagedSql(String countsql, String sql, PageInfo pageInfo, Map<String, Object> countparamMap,
            Map<String, Object> paramMap) throws Exception {
        String countSql = "select count(1) from (" + countsql + ") tmp__table ";
        int totalRecords = (Integer) this.queryForObject(countSql, countparamMap, Integer.class);
        pageInfo.setTotal(totalRecords);
        int pageRecords = pageInfo.getRowCount();
        int currentPage = pageInfo.getCurrentPage();
        int totalPages = 0;
        if (totalRecords % pageRecords == 0) {
            totalPages = totalRecords / pageRecords;
        } else {
            totalPages = totalRecords / pageRecords + 1;
        }

        pageInfo.setAllPage(totalPages);
        pageInfo.setCurrentPage(currentPage);
        int skipCount = (currentPage - 1) * pageRecords;
        int stopCount = currentPage * pageRecords;
        return this.getRangedSql(sql, skipCount, stopCount);
    }

    public <T> List<T> queryForListPaged(String sql, Map<String, Object> paramMap, PageInfo pageInfo,
            RowMapper<T> rowMapper) {
        String sqlPaged = this.getPagedSql(sql, pageInfo, paramMap);
        List<T> data = this.query(sqlPaged, paramMap, rowMapper);
        return data;
    }

    private String getPagedSql(String sql, PageInfo pageInfo, Map<String, Object> paramMap) {
        String dbType = getDBType(this.dynamicDatasource);
        StringBuffer sqlbak = new StringBuffer();
        sqlbak.append(sql);
        String tmpSql;
        int pageRecords;
        int currentPage;
        if (dbType.equals(DB_TYPE_SQLSERVER)) {
            tmpSql = sql.toUpperCase().trim();
            if (tmpSql.startsWith("WITH")) {
                throw new RuntimeException("SQL SERVER数据库使用临时表WITH AS的分页查询请使用PagedJdbcTemplate.queryForListPagedEx方法");
            }

            String orderSql = "";
            if (sql.toUpperCase().contains(" ORDER ")) {
                pageRecords = sql.toUpperCase().lastIndexOf(" ORDER ");
                orderSql = sql.substring(pageRecords, sql.length());
                sql = sql.substring(0, pageRecords);
                if (orderSql.contains(")")) {
                    currentPage = orderSql.indexOf(")");
                    sql = sql + orderSql.substring(currentPage, orderSql.length() - currentPage);
                    orderSql = orderSql.substring(0, currentPage);
                }

                sql = sql.replaceAll(orderSql, "");
            }
        }

        tmpSql = "select count(1) from (" + sql + ") tmp__table ";
        int totalRecords = (Integer) this.queryForObject(tmpSql, paramMap, Integer.class);
        pageInfo.setTotal(totalRecords);
        pageRecords = pageInfo.getRowCount();
        currentPage = pageInfo.getCurrentPage();
        int totalPages;
        if (totalRecords % pageRecords == 0) {
            totalPages = totalRecords / pageRecords;
        } else {
            totalPages = totalRecords / pageRecords + 1;
        }

        if (totalPages != 0 && totalPages != 1) {
            if (currentPage != 1 && currentPage > totalPages) {
                currentPage = totalPages;
            }
        } else {
            currentPage = 1;
        }

        pageInfo.setAllPage(totalPages);
        pageInfo.setCurrentPage(currentPage);
        int skipCount = (currentPage - 1) * pageRecords;
        int stopCount = currentPage * pageRecords;
        return this.getRangedSql(sqlbak.toString(), skipCount, stopCount);
    }

    private String getPagedSqlEx(String sql, String countSql, PageInfo pageInfo, Map<String, Object> paramMap) {
        int totalRecords = (Integer) this.queryForObject(countSql, paramMap, Integer.class);
        pageInfo.setTotal(totalRecords);
        int pageRecords = pageInfo.getRowCount();
        int currentPage = pageInfo.getCurrentPage();
        int totalPages;
        if (totalRecords % pageRecords == 0) {
            totalPages = totalRecords / pageRecords;
        } else {
            totalPages = totalRecords / pageRecords + 1;
        }

        if (totalPages != 0 && totalPages != 1) {
            if (currentPage != 1 && currentPage > totalPages) {
                currentPage = totalPages;
            }
        } else {
            currentPage = 1;
        }

        pageInfo.setAllPage(totalPages);
        pageInfo.setCurrentPage(currentPage);
        int skipCount = (currentPage - 1) * pageRecords;
        int stopCount = currentPage * pageRecords;
        return this.getRangedSql(sql, skipCount, stopCount);
    }

    public String getRangedSql(String sql, int fromRow, int toRow) {
        StringBuilder sb = new StringBuilder();
        String dbType = getDBType(this.dynamicDatasource);
        if (dbType.equals(DB_TYPE_ORACLE)) {
            sb.append("SELECT * FROM (  ");
            sb.append("    SELECT tmp__table.*, ROWNUM as row_num ");
            sb.append("    FROM (").append(sql).append(") tmp__table  ");
            sb.append("    WHERE ROWNUM <= ").append(toRow);
            sb.append("              ) ");
            sb.append("WHERE row_num > ").append(fromRow);
        } else {
            int pageSize;
            if (dbType.equals(DB_TYPE_MYSQL)) {
                sb.append(sql);
                pageSize = toRow - fromRow;
                sb.append(" LIMIT ").append(fromRow).append(", ").append(pageSize);
            } else if (dbType.equals(DB_TYPE_SQLSERVER)) {
                if (sql.toUpperCase().contains(" ORDER ")) {
                    pageSize = toRow - fromRow;
                    sb.append(sql).append(" offset ").append(fromRow).append(" rows fetch next ").append(pageSize)
                            .append(" rows only");
                } else {
                    pageSize = toRow - fromRow;
                    sb.append(sql).append(" order by 1 offset ").append(fromRow).append(" rows fetch next ")
                            .append(pageSize).append(" rows only");
                }
            }
        }

        return sb.toString();
    }

    public List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource)
            throws DataAccessException {
        return this.lowerCaseColumn
                ? super.queryForList(sql, paramSource)
                : this.query(sql, paramSource, new UpperCaseColumnMapRowMapper());
    }

    public Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource) throws DataAccessException {
        return this.lowerCaseColumn
                ? super.queryForMap(sql, paramSource)
                : (Map) this.queryForObject(sql, paramSource, new UpperCaseColumnMapRowMapper());
    }

    public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) throws DataAccessException {
        return this.lowerCaseColumn
                ? super.queryForMap(sql, paramMap)
                : (Map) this.queryForObject(sql, paramMap, new UpperCaseColumnMapRowMapper());
    }

    public <T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
            throws DataAccessException {
        List<T> lists = this.getJdbcOperations().query(this.getPreparedStatementCreator(sql, paramSource), rowMapper);
        this.printQueryLineCount(sql, paramSource, lists);
        return lists;
    }

    private <T> void printQueryLineCount(String sql, SqlParameterSource paramSource, List<T> lists) {
        int count = 0;
        if (lists != null && !lists.isEmpty()) {
            count = lists.size();
        }

        if (count > 500) {
            StringBuilder sb = new StringBuilder("查询sql: ");
            sb.append(sql);
            sb.append("\r\n查询结果行数: ");
            sb.append(count + "");
            logger.warn(sb.toString());
        }

    }

    public boolean isLowerCaseColumn() {
        return this.lowerCaseColumn;
    }

    public void setLowerCaseColumn(boolean lowerCaseColumn) {
        this.lowerCaseColumn = lowerCaseColumn;
    }
}